const express = require('express');
const mysql = require('mysql2');

const app = express();
app.use(express.json()); // 用于解析 JSON 格式的请求体
app.use(express.static('public'));

// 配置数据库信息
var connection = mysql.createConnection({
  host: 'localhost',
  port: '3306',
  user: 'root',
  password: 'root', // 你自己数据库root用户的密码
  database: 'web_foundation'
});

// 连接
connection.connect();

app.get('/', function(request, response) {
    response.send('Hello world');
});

app.get('/simple-html-page', function(request, response) {
    response.send(`<!DOCTYPE html>
        <html>
          <head>
            <title>Simple HTML page</title>
          </head>
          <body>
            <h1>Simple HTML page</h1>
          </body>
        </html>`)
});

app.get('/students', function(request, response) {
  const sql = 'SELECT * FROM tb_students';
  connection.query(sql, function(error, data) {
    if(error) {
      response.status(500).send({
        message: '查询数据错误'
      })
      console.error(error);
    } else {
      response.json({
        message: 'success',
        data: data
      });
    }
  })    
});

app.post('/students/add', function(request, response) {
    var newStudent = request.body;
    const sql = `INSERT INTO tb_students (studentNo, name, class, gender)
                  VALUES (?, ?, ?, ?)`;
    const sqlParams = [
      newStudent.studentNo,
      newStudent.name,
      newStudent.class,
      newStudent.gender
    ];
    connection.query(sql, sqlParams, function(error, data) {
      if(error) {
        response.status(500).send({
          message: '执行数据库操作时遇到错误'
        })
        console.error(error);
      } else {
        response.json({
          message: 'success'
        })
      }
    })
});

app.get('/students/delete', function(request, response) {
  var studentNo = request.query.studentNo;
  const sql = 'DELETE FROM tb_students WHERE studentNo = ?';
  const sqlParams = [studentNo];
  connection.query(sql, sqlParams, function(error, data) {
    if(error) {
      response
      .status(500)
      .json({
        message: '执行数据库操作时遇到错误'
      });
      console.error(error);
    } else {
      response.json({
        message: 'success'
      })
    }
  })
});

app.post('/students/edit', function(request, response) {
  var newStudent = request.body;
  const sql = 'UPDATE tb_students ' +
              '   SET name = ?, class = ?, gender = ? ' +
              ' WHERE studentNo = ?';
  const sqlParams = [
    newStudent.name,
    newStudent.class,
    newStudent.gender,
    newStudent.studentNo
  ];
  connection.query(sql, sqlParams, function(error, data) {
    if(error) {
      response
      .status(500)
      .json({
        message: '执行数据库操作时遇到错误'
      });
      console.error(error);
    } else {
      response.json({
        message: 'success'
      })
    }
  })
});

const port = 3000;
app.listen(port, function() {
    console.log('我的第一个Web服务器，运行于http://localhost:3000')
})